package com.db;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Timer;
import java.util.TimerTask;
import java.util.concurrent.CopyOnWriteArrayList;

import net.sf.json.JSONObject;

import com.log.LogUtil;

public class DbUtil {

	private static Properties prop = new Properties();
	private static String driverClass = null;
	private static String url = null;
	private static String username = null;
	private static String password = null;
	private static int maxIdle = 0;
	private static int maxActive = 0;
	private static final CopyOnWriteArrayList<Map<String, Object>> list = new CopyOnWriteArrayList<Map<String, Object>>();
	private static final Timer timer = new Timer("数据库连接管理时钟");
	private static final TimerTask ADD_TASK = new TimerTask() {		
		@Override
		public void run() {
			for (int i = list.size(); i < (maxIdle<=0?10:maxIdle); i++) {
					HashMap<String, Object> map = new HashMap<String, Object>();
					map.put("con", getCon());
					map.put("status", 1);
					map.put("idle", new Date().getTime());
					list.add(map);
			}			
		}
	};
	private static final TimerTask clearTask=new TimerTask() {
		
		@Override
		public void run() {
			for (Map<String, Object> map : list) {
				// 清除标记为3的数据库连接
				if (Integer.parseInt(map.get("status").toString())==3){
					Connection con=(Connection)map.get("con");
					try {
						con.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					list.remove(map);
				}
			}			
		}
	};
	private static final TimerTask setStatusTask = new TimerTask() {
		@Override
		public void run() {
			long time = new Date().getTime();
			for (Map<String, Object> map : list) {
				// 超过30秒空闲的连接标记为3
				if (time - Long.parseLong(map.get("idle").toString()) > 50 * 1000
						&& Integer.parseInt(map.get("status").toString()) == 1) {									
					map.put("status", 3);
				}
			}			
		}
	};

	static {
		InputStream is = DbUtil.class.getResourceAsStream("/com/config/config.properties");
		try {
			prop.load(is);
			is.close();
			driverClass = prop.getProperty("driverClass");
			url = prop.getProperty("url");
			username = prop.getProperty("username");
			password = prop.getProperty("password");
			maxIdle = prop.getProperty("maxIdle") == null ? 0 : Integer.parseInt(prop.getProperty("maxIdle"));
			maxActive = prop.getProperty("maxActive") == null ? 0 : Integer.parseInt(prop.getProperty("maxActive"));
			Class.forName(driverClass);
			timer.schedule(ADD_TASK, 20 * 1000, 30 * 1000);
			timer.schedule(setStatusTask, 30 * 1000, 30 * 1000);
			timer.schedule(clearTask, 40 * 1000, 30 * 1000);
		} catch (Exception e) {
			LogUtil.warn(e);
		}
	}

	private static Connection getCon() {
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, username, password);
			//LogUtil.info("实例个数："+(list.size()+1)+"-"+con);
		} catch (SQLException e) {
			LogUtil.warn(e);
		}
		return con;
	}

	public static Connection getConnection() {
		Connection con = getIdleConn();
		if (con == null) {
			int listSize = list.size();
			if (maxActive == 0 || listSize < maxActive) {
				// 没有成功获得空间的连接，说明空闲的连接不够用了，需要增加新的连接
				HashMap<String, Object> map = new HashMap<String, Object>();
				con = getCon();
				map.put("con", con);
				map.put("status", 2);
				map.put("idle", new Date().getTime());
				list.add(map);
			} else if(listSize >= maxActive){
				throw new RuntimeException("获取connection连接数超限:"+listSize+" >= "+maxActive);
			}
		}
		return con;
	}

	private static Connection getIdleConn() {
		Connection con = null;
		// 取一个空闲的连接
		for (Map<String, Object> map : list) {
			if (Integer.parseInt(map.get("status").toString()) == 1) {
				map.put("status", 2);
				con = (Connection) map.get("con");
				break;
			}
		}
		return con;
	}

	public static int insert(Map<String, ?> map) throws SQLException {
		int count = 0;
		StringBuffer fieldSql = new StringBuffer();
		StringBuffer valueSql = new StringBuffer();
		Iterator<String> it = map.keySet().iterator();
		if (!map.containsKey("tableName")) {
			throw new SQLException("tableName must be contained in map's keyset when insert ");
		}
		String tableName = map.get("tableName").toString();
		while (it != null && it.hasNext()) {
			String key = it.next();
			if (key.equals("tableName"))
				continue;
			fieldSql.append(key).append(",");
			valueSql.append(map.get(key) == null ? "null" : "'"	+ map.get(key).toString() + "'").append(",");
		}
		if (fieldSql.length() > 0 && valueSql.length() > 0) {
			String sql = fieldSql.deleteCharAt(fieldSql.length()-1).insert(0, "insert into " + tableName + "(").append(") values (").append(valueSql.deleteCharAt(valueSql.length()-1)).append(")")	.toString();
			count = updateBySql(sql);
		}
		return count;
	}

	public static int insert(String sql) throws SQLException {
		return updateBySql(sql);
	}
	public static int insert(List<Map<String ,?>> list) throws SQLException{
		int count = 0;
		if (list.size() == 0)	return 0;
		Connection con=getConnection();
		Statement st=con.createStatement();
		for (Map<String, ?> map : list) {
			StringBuffer fieldSql = new StringBuffer();
			StringBuffer valueSql = new StringBuffer();
			Iterator<String> it = map.keySet().iterator();
			if (!map.containsKey("tableName")) {
				throw new SQLException("tableName must be contained in map's keyset when insert ");
			}
			String tableName = map.get("tableName").toString();
			while (it != null && it.hasNext()) {
				String key = it.next();
				if (key.equals("tableName")) continue;
				fieldSql.append(key).append(",");
				valueSql.append(map.get(key) == null ? "null" : "'"	+ map.get(key).toString() + "'").append(",");
			}
			if (fieldSql.length() > 0 && valueSql.length() > 0) {
				String sql = fieldSql.deleteCharAt(fieldSql.length()-1).insert(0, "insert into " + tableName + "(")
						.append(") values (").append(valueSql.deleteCharAt(valueSql.length()-1)).append(")").toString();	
				st.addBatch(sql);
			}
		}
		int total[]=st.executeBatch();
		for(int i:total){
			count+=i;
		}
		closeCon(con);
		return count;
	}
	public static int queryForInt(String sql) throws SQLException {
//		long start=System.currentTimeMillis();
		Long count = queryForObject(sql, Long.class);
//		long end=System.currentTimeMillis();
		//LogUtil.info("cost="+(end-start));
		if (count != null)
			return Integer.parseInt(""+count);
		else
			return 0;
	}

	@SuppressWarnings("unchecked")
	public static <T> List<T> queryForList(String sql, Class<T> c)	throws SQLException {
		List<T> list = new ArrayList<T>();
		Connection con=getConnection();
		ResultSet rs = con.createStatement().executeQuery(sql);
		while (rs != null && rs.next()) {
			list.add((T)rs.getObject(1));
		}
		closeCon(con);
		return list;
	}
	@SuppressWarnings("unchecked")
	public static <T> List<T> queryForListOne(String sql, Class<T> c)	throws SQLException {
		List<T> list = new ArrayList<T>();
		Connection con=getConnection();
		ResultSet rs = con.createStatement().executeQuery(sql);		
		if(rs != null && rs.next() && rs.last()) {
			for(int i=1;i<=rs.getMetaData().getColumnCount();i++){
				list.add((T)rs.getObject(i));				
			}
		}
		closeCon(con);
		return list;
	}

	@SuppressWarnings("unchecked")
	public static <T>List<Map<String, T>> queryForListMap(String sql,Class<T> c)	throws SQLException {
		List<Map<String, T>> list = new ArrayList<Map<String, T>>();
		Connection con=getConnection();
		ResultSet rs = con.createStatement().executeQuery(sql);
		ResultSetMetaData rmd = rs == null ? null : rs.getMetaData();
		while (rs != null && rs.next()) {
			Map<String, T> map = new HashMap<String, T>();
			for (int i = 1; i <= rmd.getColumnCount(); i++) {
				map.put(rmd.getColumnName(i), (T)rs.getObject(i));
			}
			list.add(map);
		}
		closeCon(con);
		return list;
	}

	@SuppressWarnings("unchecked")
	public static <T> Map<String, T> queryForMap(String sql,Class<T> c)	throws SQLException {
		Map<String, T> map = new HashMap<String, T>();
		Connection con=getConnection();
		ResultSet rs = con.createStatement().executeQuery(sql);
		if (rs != null && rs.next() && rs.last()) {
			ResultSetMetaData rmd = rs.getMetaData();
			for (int i = 1; i <= rmd.getColumnCount(); i++) {
				map.put(rmd.getColumnName(i), (T)rs.getObject(i));
			}
		}
		closeCon(con);		
		return map;
	}	
	@SuppressWarnings("unchecked")
	public static <T> Map<String, T> queryForMaps(String sql,Class<T> c)	throws SQLException {
		Map<String, T> map = new HashMap<String, T>();
		Connection con=getConnection();
		ResultSet rs = con.createStatement().executeQuery(sql);
		while (rs != null && rs.next() ) {
			map.put(rs.getString(1), (T)rs.getObject(2));
		}
		closeCon(con);
		return map;
	}	

	@SuppressWarnings("unchecked")
	public static <T> T queryForObject(String sql, Class<T> c)	throws SQLException {
		T t = null;
		Connection con=getConnection();
		ResultSet rs = con.createStatement().executeQuery(sql);
		if (rs != null && rs.next() && rs.last()) {
			t = (T)rs.getObject(1);
		}
		closeCon(con);
		return t;
	}
	public static ResultSet queryForRS(String sql,Connection con) throws SQLException{
		return con.createStatement().executeQuery(sql);		
	}
	public static String queryForString(String sql) throws SQLException {
		return queryForObject(sql, String.class);
	}

	public static int update(String sql) throws SQLException {
		return updateBySql(sql);
	}

	public static int updateByMap(Map<String, ?> map, String whereStr)
			throws SQLException {
		int count = 0;
		StringBuffer sql = new StringBuffer();
		Iterator<String> it = map.keySet().iterator();
		if (!map.containsKey("tableName")) {
			throw new SQLException("tableName must be contained in map's keyset when update ");
		}
		String tableName = map.get("tableName").toString();
		while (it != null && it.hasNext()) {
			String key = it.next();
			if (key.equals("tableName"))
				continue;
			sql.append(key).append("=").append(map.get(key) == null ? "null" : "'"+ map.get(key).toString() + "'").append(",");
		}
		if (sql.length() > 0) {
			sql.insert(0, "update " + tableName + " set ").deleteCharAt(sql.length()-1).append(" where 1=1 ").append(whereStr);
			count = updateBySql(sql.toString());
		}
		return count;
	}
	
	private static int updateBySql(String sql) throws SQLException {
		int count = 0;
		Connection con=getConnection();
		count = con.createStatement().executeUpdate(sql);
		closeCon(con);
		return count;
	}

	public static void closeCon(Connection con) {
		for(Map<String,Object> map:list){
			if(map.get("con")==con){
				map.put("status", 1);
				map.put("idle", new Date().getTime());
			}
		}
			
		
	}
	/**
	 * @param args
	 * @throws SQLException
	 */
	public static void main(String[] args) throws SQLException {
		//getConnection();
		//System.out.println(queryForList("select id from lottery.data1", Integer.class));
		JSONObject obj=new JSONObject();
		obj.accumulate("a", null);
		//obj.accumulate("a", 12);
		System.out.println(obj+","+obj.containsKey("a"));
	}
}
